from flask import Blueprint, request,current_app,g
from utilsapp.mysql_con import db_connect, db_list
import json
from utilsapp import utils

bluePrint = Blueprint('openapicommon', __name__)

@bluePrint.before_request
def before_request():
    key = request.args.get('key')
    id = request.args.get('id',None)

    config = current_app.config['settings']
    g.id=0
    if 'api_key' in config and len(config['api_key']) > 0:
        if key != config['api_key']:
            return utils.fail('key 参数不正确')
        try:
            g.id = int(utils.aes_decrypt(utils.base64_decode(id)))
        except:
            g.id=0

        if g.id<=0:
            return utils.fail('id 参数不正确')
        

@bluePrint.route('/common/orglist', methods=["get","post"])
def orglist():
    id = getattr(g, 'id', 0)
    if id<=0:
        sql = '''
        SELECT id as org_id,`name` as org_name,case type WHEN 'company' then '公司'  WHEN 'group' then '集团'  
        WHEN 'factory' then '工厂' WHEN 'zone' then '区域' else '' end as org_type,parent as pid  
        FROM regionlevel ORDER BY id,pid,type    
        '''
        return db_list(sql)
    return org_id_list(id)

def org_id_list(id):
    sql = f'''
        WITH RECURSIVE org_hierarchy AS (
        -- 基础查询：获取起始节点
        SELECT 
            id as org_id,
            `name` as org_name,
            CASE type 
                WHEN 'company' THEN '公司'
                WHEN 'group' THEN '集团'
                WHEN 'factory' THEN '工厂'
                WHEN 'zone' THEN '区域'
                ELSE ''
            END as org_type,
            parent as pid,
            0 as level  -- 添加一个级别字段，表示当前节点的深度
        FROM regionlevel
        WHERE id = {id}  -- 指定起始节点的 ID

        UNION ALL

        -- 递归部分：查找子节点
        SELECT 
            r.id as org_id,
            r.`name` as org_name,
            CASE r.type 
                WHEN 'company' THEN '公司'
                WHEN 'group' THEN '集团'
                WHEN 'factory' THEN '工厂'
                WHEN 'zone' THEN '区域'
                ELSE ''
            END as org_type,
            r.parent as pid,
            oh.level + 1  -- 级别加 1
        FROM regionlevel r
        INNER JOIN org_hierarchy oh ON r.parent = oh.org_id
    )
    SELECT 
        org_id,
        org_name,
        org_type,
        pid
    FROM org_hierarchy
    ORDER BY level, org_id;
    '''
    return db_list(sql)
    

@bluePrint.route('/common/devicelist', methods=["get","post"])
def devicelist():
    id = getattr(g, 'id', None)
    where = '' if id <=0 else f' where d.group_id = {id}'
    
    sql = f'''
    SELECT d.id as device_id, d.device_name, d.device_code, d.zone_id as org_id,
    CASE WHEN d.`status` = 0 OR d.`status` = 3 THEN 0 ELSE 1 END as device_run,
    tb.device_warn_level, h.health_grade  -- 添加 health_grade 字段
    FROM device d
    LEFT JOIN (
        SELECT max(warn_level) as device_warn_level, device_id FROM warn 
        WHERE is_show = 1 AND is_handled = 0
        GROUP BY device_id
    ) tb ON tb.device_id = d.id
    LEFT JOIN (
        SELECT h.device_id, h.health_grade
        FROM health h
        JOIN (
            SELECT device_id, MAX(id) AS max_id
            FROM health
            GROUP BY device_id
        ) latest_health
        ON h.device_id = latest_health.device_id AND h.id = latest_health.max_id
    ) h ON h.device_id = d.id

    {where}
    ORDER BY d.id  
    '''

    return db_list(sql)

@bluePrint.route('/common/pointslist', methods=["get","post"])
def pointslist():
    id = getattr(g, 'id', None)
    where = '' if id <=0 else f' where s.group_id = {id}'
    sql = f'''
    SELECT s.id as sensor_id,d.device_name, s.device_id,d.device_code,s.`sensor_name` as sensor_name,
    CONCAT(s.`port`,'_',s.address) as sensor_code,s.direction,s.status FROM sensor s 
    LEFT JOIN device d on s.device_id=d.id
    {where}
    ORDER BY s.id
    '''
    return db_list(sql)

@bluePrint.route('/common/measuredata', methods=["get","post"])
def measuredata():
    id = getattr(g, 'id', None)
    where = '' if id <=0 else f' where s.group_id = {id}'
    sql = f'''
    SELECT  CONCAT(s.`port`,'_',s.address) as sensor_code,d.device_code,
    IF(ISNULL(tb.warn_level)=1,null,tb.warn_level) as warn_level,  '' as vibvalue,'mm/s' as vibunit,'' as tempvalue,st.topic,s.`port`,s.address,
    '℃' as tempunit,'' as `time` FROM sensor s 
    LEFT JOIN sensor_type st on s.sensor_type=st.id
    LEFT JOIN device d on s.device_id=d.id
    LEFT JOIN(
    SELECT max(warn_level) as warn_level,sensor_id FROM warn 
    where is_show = 1 and is_handled = 0
    GROUP BY sensor_id) tb on tb.sensor_id=s.id
    {where}
    ORDER BY s.id
    '''
    res =  db_list(sql)
    if res['code'] !=0:
        return res
    arr = res['data']
    for t in arr:
        strj = current_app.redis.get(f'{t["topic"]}{t["port"]}/{t["address"]}')
        del t['topic']
        del t['port']
        del t['address']
        if strj:
            j = json.loads(strj)
            t['vibvalue'] =j.get('vel_rms_z',None)
            t['tempvalue'] =j.get('tem',None)
            t['audio'] =j.get('audio',None)
            t['a_cur'] =j.get('a_cur',None)
            t['b_cur'] =j.get('b_cur',None)
            t['c_cur'] =j.get('c_cur',None)
            t['vibstandard'] = [8 * 1.2, 8 * 1.5, 8 * 1.8]
            t['tempstandard'] = [50 * 1.2, 50 * 1.5, 50 * 1.8]
            t['time'] =j.get('time',None)
    return utils.ok(arr)




@bluePrint.route('/common/warnevents', methods=["get","post"])
def warnevents():

    id = getattr(g, 'id', None)
    where = '' if id <=0 else f'AND w.group_id = {id}'

    MinDate = request.args.get('MinDate',None)
    MaxDate = request.args.get('MaxDate',None)
    if MinDate is None or MaxDate is None:
        return utils.fail('参数不正确')

    sql = f'''
    SELECT w.id as warn_id,d.device_code,CONCAT(s.`port`,'_',s.address) as sensor_code,w.warn_level,w.create_time as warn_create_time
    ,w.pred_time as warn_pred_time,case is_handled when 3 THEN 2 else is_handled end as warn_state,
    case is_handled when 3 THEN '已恢复正常'  when 1 THEN '已处理' when 2 THEN '已处理' else '' end as warn_reason,
    content,cause,suggest,standard_data,pred_data FROM warn w
    LEFT JOIN sensor s on w.sensor_id = s.id
    LEFT JOIN device d on w.device_id=d.id
    where 1=1 AND w.create_time BETWEEN '{MinDate}' and '{MaxDate}' {where}
    ORDER BY w.id
    '''
    return db_list(sql)


@bluePrint.route('/common/reportlist', methods=["get","post"])
def reportlist():
    id = getattr(g, 'id', None)
    where = '' if id <=0 else f'where r.group_id = {id}'
    sql = f'''
    SELECT r.id as report_id ,d.device_code , r.maker,r.type,
    CONCAT('/static/upload/',r.filepath) as filepath,r.create_time as `time` FROM report r 
    LEFT JOIN device d on d.id=r.device_id
    {where}
     ORDER BY r.id
    '''
    return db_list(sql)

@bluePrint.route('/common/liehua', methods=["post"])
def liehua1():
    device_id = request.json['device_id']
    return db_connect(f"select time, (100 - health_grade) AS liehua_grade from health where device_id = {device_id} ORDER BY time DESC LIMIT 30")